﻿Option Strict On

Imports System.Data
Imports System.Data.Common
Imports DbDataLib
Imports KUeLib.Kontoauszüge

Namespace Services

    Public Class KontoauszugDataService
        Inherits ServiceDefinitions.KontoauszugDataService

        Public Sub New(ByVal dataService As DbDataService)
            MyBase.New(dataService)
        End Sub

        Protected Function GetLastInsertId() As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("SELECT last_insert_id()")

            cmd.CommandText = sb.ToString()

            Return CInt(_dataService.ExecuteScalar(cmd))
        End Function

        Public Overrides Function GetAlleKontoauszüge() As List(Of Kontoauszug)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder()
            Dim dt As DataTable
            Dim lok As New List(Of Kontoauszug)

            sb.AppendLine("SELECT ka_id, ka_filename, ka_bezeichnung, ka_von, ka_bis FROM kontoauszuege ")

            cmd.CommandText = sb.ToString()
            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1
                With dt.Rows(i)
                    Dim tk As New Kontoauszug
                    tk.Id = CInt(Utils.DbNull2Nothing(.Item("ka_id")))
                    tk.Bezeichnung = CStr(Utils.DbNull2Nothing(.Item("ka_bezeichnung")))
                    tk.Filename = CStr(Utils.DbNull2Nothing(.Item("ka_filename")))
                    tk.Von = CDate(Utils.DbNull2Nothing(.Item("ka_von")))
                    tk.Bis = CDate(Utils.DbNull2Nothing(.Item("ka_bis")))
                    tk.Binary = Nothing

                    lok.Add(tk)
                End With
            Next

            Return lok

        End Function

        Public Overrides Function GetKontoauszugById(ByVal id As Integer) As Kontoauszug
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder()
            Dim dt As DataTable
            Dim tk As Kontoauszug = Nothing

            sb.AppendLine("SELECT ka_id, ka_filename, ka_bezeichnung, ka_von, ka_bis FROM kontoauszuege WHERE ka_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1
                With dt.Rows(i)
                    tk = New Kontoauszug
                    tk.Id = CInt(Utils.DbNull2Nothing(.Item("ka_id")))
                    tk.Bezeichnung = CStr(Utils.DbNull2Nothing(.Item("ka_bezeichnung")))
                    tk.Filename = CStr(Utils.DbNull2Nothing(.Item("ka_filename")))
                    tk.Von = CDate(Utils.DbNull2Nothing(.Item("ka_von")))
                    tk.Bis = CDate(Utils.DbNull2Nothing(.Item("ka_bis")))
                    tk.Binary = Nothing
                End With
            Next

            Return tk

        End Function

        Public Overrides Function Insert(ByVal kontoauszug As Kontoauszug) As Kontoauszug
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("INSERT INTO kontoauszuege(ka_bezeichnung, ka_filename, ka_von, ka_bis) ")
            sb.AppendLine(" VALUES( @p_bezeichnung, @p_filename, @p_von, @p_bis ) ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, kontoauszug.Bezeichnung))
            cmd.Parameters.Add(_dataService.CreateParameter("p_filename", DbType.String, kontoauszug.Filename))
            cmd.Parameters.Add(_dataService.CreateParameter("p_von", DbType.DateTime, kontoauszug.Von))
            cmd.Parameters.Add(_dataService.CreateParameter("p_bis", DbType.DateTime, kontoauszug.Bis))

            _dataService.ExecuteNonQuery(cmd)

            kontoauszug.Id = GetLastInsertId()

            If kontoauszug.Binary IsNot Nothing Then _
                SaveBinary(kontoauszug)

            Return kontoauszug

        End Function

        Public Overloads Overrides Function Update(ByVal kontoauszug As Kontoauszug) As Integer
            Return Update(kontoauszug, False)
        End Function

        Public Overloads Overrides Function Update(ByVal kontoauszug As Kontoauszug, ByVal updateBinary As Boolean) As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("UPDATE kontoauszuege SET ka_bezeichnung = @p_bezeichnung, ka_filename = @p_filename, ka_von = @p_von, ka_bis = @p_bis ")
            sb.AppendLine("WHERE ka_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, kontoauszug.Bezeichnung))
            cmd.Parameters.Add(_dataService.CreateParameter("p_filename", DbType.String, kontoauszug.Filename))
            cmd.Parameters.Add(_dataService.CreateParameter("p_von", DbType.DateTime, kontoauszug.Von))
            cmd.Parameters.Add(_dataService.CreateParameter("p_bis", DbType.DateTime, kontoauszug.Bis))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, kontoauszug.Id))

            Update = _dataService.ExecuteNonQuery(cmd)

            If updateBinary AndAlso Update > 0 Then _
                SaveBinary(kontoauszug)

        End Function

        Public Overloads Overrides Sub SaveBinary(ByVal kontoauszug As Kontoauszug)
            SaveBinary(kontoauszug.Id, kontoauszug.Binary)
        End Sub

        Public Overloads Overrides Sub SaveBinary(ByVal id As Integer, ByVal b() As Byte)
            If Not CheckIdExists(id) Then _
                            Throw New NullReferenceException("Der Kontoauszug existiert im aktuellen Datenbestand nicht. Bitte legen Sie ihn ggf. erst an.")

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("UPDATE kontoauszuege SET ka_binary = @p_binary WHERE ka_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_binary", DbType.Binary, b))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            _dataService.ExecuteNonQuery(cmd)

        End Sub

        Public Overrides Sub FillBinary(ByRef kontoauszug As Kontoauszug)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder()
            Dim dt As DataTable

            sb.AppendLine("SELECT ka_binary FROM kontoauszuege WHERE ka_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, kontoauszug.Id))

            dt = _dataService.GetDataTable(cmd)

            If dt.Rows.Count > 0 Then
                kontoauszug.Binary = CType(Utils.DbNull2Nothing(dt.Rows(0).Item("ka_binary")), Byte())
            Else
                kontoauszug.Binary = Nothing
            End If

        End Sub

        Public Overrides Function CheckIdExists(ByVal id As Integer) As Boolean
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.Append("SELECT count(*) FROM kontoauszuege WHERE ka_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            Return (CInt(_dataService.ExecuteScalar(cmd)) > 0)

        End Function

        Public Overrides Function Delete(ByVal id As Integer) As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("DELETE FROM kontoauszuege WHERE ka_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, id))

            Return _dataService.ExecuteNonQuery(cmd)
        End Function

        Public Overrides Function Delete(ByVal kontoauszug As Kontoauszug) As Integer
            Return Delete(kontoauszug.Id)
        End Function

    End Class

End Namespace
